-- @owner: zhao-pengcheng-g
-- @date: 2022/01/24
-- @testpoint:对视图做delete,insert操作，触发trigger，多个触发事件

--step1:建表;expect:成功
drop table if exists table_src_0006 cascade;
CREATE TABLE table_src_0006(id1 INT, id2 INT, id3 INT) WITH (STORAGE_TYPE=USTORE)
partition by range(id2)
(
        partition p1 values less than(1),
        partition p2 values less than(100000),
        partition p3 values less than(maxvalue)
);

--step2:创建视图，创建函数，创建触发器;expect:成功
create or replace view test_trigger_src_view1_0006 as select * from table_src_0006;
CREATE OR REPLACE FUNCTION INSERT_FUNC_0006()
RETURNS TRIGGER AS $$
BEGIN
if(TG_OP='DELETE')
then
delete from table_src_0006 where id1=1;
RETURN NEW;
end if;
if(TG_OP='INSERT')
then
insert into table_src_0006 values(new.id1,new.id2,new.id3);
RETURN NEW;
end if;
END;
$$LANGUAGE plpgsql;
/

drop trigger if exists INSERT_TRIGGER_0006 on test_trigger_src_view1_0006;
CREATE TRIGGER INSERT_TRIGGER_0006
INSTEAD OF delete or insert ON test_trigger_src_view1_0006
FOR EACH row
EXECUTE PROCEDURE INSERT_FUNC_0006();
/

--step3:对视图做delete,insert操作，触发trigger;expect:成功
insert into table_src_0006 values(1,1,1),(2,2,2),(3,3,3);
select * from test_trigger_src_view1_0006 order by id1;
delete from test_trigger_src_view1_0006 where id1=2;
select * from test_trigger_src_view1_0006 order by id1;
insert into test_trigger_src_view1_0006 values(4,4,4);
select * from test_trigger_src_view1_0006 order by id1;

--step4:环境清理;expect:成功
drop TRIGGER INSERT_TRIGGER_0006 on test_trigger_src_view1_0006 cascade;
drop table table_src_0006 cascade;
drop FUNCTION INSERT_FUNC_0006;

